﻿<%@ WebHandler Language="C#" Class="RSFactoryMonthCapacityDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
using System.Collections;

/**
 * Description: 燃烧部件事业部月度产能看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2022.9.2
 */

public class RSFactoryMonthCapacityDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getWorkcenterChartData":
                        r = getWorkcenterChartData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getTeamChartData":
                        r = getTeamChartData(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    Result getWorkcenterChartData(JObject jObject)
    {
        StringBuilder sb = new StringBuilder();
        //查询工区--计划产能
        sb.AppendLine("SELECT w.workcentername,COUNT(e.fullname) * 180 planedTime");
        sb.AppendLine("FROM employee e ");
        sb.AppendLine("LEFT JOIN employeerole er ON er.employeeid = e.employeeid");
        sb.AppendLine("LEFT JOIN roledef rd ON rd.roleid = er.roleid");
        sb.AppendLine("LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid");
        sb.AppendLine("LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid");
        sb.AppendLine("WHERE (rd.rolename='工人' OR rd.rolename='班组长') AND w.workcentername IS NOT null ");
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sb.AppendFormat(" AND sv.factoryid = '{0}'", jObject["factoryid"]);
        }
        sb.AppendLine("GROUP BY w.workcentername");
        sb.AppendLine("ORDER BY w.workcentername");

        DataTable dtWorkcenterWorker = dal.GetDataTable(sb.ToString());

        sb = new StringBuilder();
        //查询工区--已完工产能
        sb.AppendLine("SELECT csp.workcentername,SUM(ws.manhours * csp.passqty) finishTime");
        sb.AppendLine("FROM containersteprecord csp");
        sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowstepid = csp.workflowstepid");
        sb.AppendLine("WHERE ws.manhours > 0 AND csp.passqty > 0 AND csp.usecompleted = 1 ");
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sb.AppendFormat(" AND csp.factoryid = '{0}'", jObject["factoryid"]);
        }

        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            sb.AppendFormat(" AND TRUNC(csp.reportdate) BETWEEN to_date('{0}','YYYY/MM/DD') AND to_date('{1}','YYYY/MM/DD') ", arr[0].ToString(), arr[1].ToString());
        }

        sb.AppendLine("GROUP BY csp.workcentername");
        sb.AppendLine("ORDER BY csp.workcentername");

        DataTable dtQueryData = dal.GetDataTable(sb.ToString());

        dtWorkcenterWorker.Columns.Add("finishTime");//实际产能
        dtWorkcenterWorker.Columns.Add("finishRate");//产能完成率

        foreach (DataRow dr in dtWorkcenterWorker.Rows)
        {
            string filter = string.Format("workcentername = '{0}'", dr["workcentername"]);

            DataRow[] drList = dtQueryData.Select(filter);

            if (drList.Length > 0)
            {
                dr["finishTime"] = Convert.ToDecimal(drList[0]["finishTime"]);
            }
            else
            {
                dr["finishTime"] = 0;
            }

            if (dr["planedTime"].ToString() == "0")
            {
                dr["finishRate"] = 0;
            }
            else
            {
                decimal finishRate = (Convert.ToDecimal(dr["finishTime"]) / Convert.ToDecimal(dr["planedTime"])) * 100;
                dr["finishRate"] = finishRate.ToString("0.00");
            }

        }

        Result r = new Result();

        foreach (DataColumn col in dtWorkcenterWorker.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dtWorkcenterWorker;
        r.total = Convert.ToInt32(dtWorkcenterWorker.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getTeamChartData(JObject jObject)
    {
        StringBuilder sb = new StringBuilder();
        //查询班组--计划产能
        sb.AppendLine("SELECT w.workcentername,t.teamname,COUNT(e.fullname) * 180 planedTime");
        sb.AppendLine("FROM employee e ");
        sb.AppendLine("LEFT JOIN employeerole er ON er.employeeid = e.employeeid");
        sb.AppendLine("LEFT JOIN roledef rd ON rd.roleid = er.roleid");
        sb.AppendLine("LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid");
        sb.AppendLine("LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid");
        sb.AppendLine("LEFT JOIN teamemployee te ON te.employeeid=e.employeeid");
        sb.AppendLine("LEFT JOIN team t ON t.teamid = te.teamid");
        sb.AppendLine("WHERE (rd.rolename='工人' OR rd.rolename='班组长') AND w.workcentername IS NOT NULL AND t.teamname IS NOT NULL ");
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sb.AppendFormat(" AND sv.factoryid = '{0}'", jObject["factoryid"]);
        }
        sb.AppendLine("GROUP BY w.workcentername,t.teamname");
        sb.AppendLine("ORDER BY w.workcentername,t.teamname");

        DataTable dtTeamname = dal.GetDataTable(sb.ToString());

        sb = new StringBuilder();
        sb.AppendLine("SELECT csp.workcentername,csp.teamname,SUM(ws.manhours * csp.passqty) finishTime");
        sb.AppendLine("FROM containersteprecord csp");
        sb.AppendLine("LEFT JOIN Workflowstep ws ON ws.workflowstepid = csp.workflowstepid");
        sb.AppendLine("WHERE ws.manhours > 0 AND csp.passqty > 0 AND csp.usecompleted = 1  AND csp.teamname IS NOT NULL ");
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sb.AppendFormat(" AND csp.factoryid = '{0}'", jObject["factoryid"]);
        }
        if (jObject["selectdate"] != null && jObject["selectdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["selectdate"].ToString());
            sb.AppendFormat(" AND TRUNC(csp.reportdate) BETWEEN to_date('{0}','YYYY/MM/DD') AND to_date('{1}','YYYY/MM/DD') ", arr[0].ToString(), arr[1].ToString());
        }
        sb.AppendLine("GROUP BY csp.workcentername,csp.teamname");
        sb.AppendLine("ORDER BY csp.workcentername,csp.teamname");

        DataTable dtQueryData = dal.GetDataTable(sb.ToString());

        dtTeamname.Columns.Add("finishTime");//实际产能
        dtTeamname.Columns.Add("finishRate");//产能完成率

        foreach (DataRow dr in dtTeamname.Rows)
        {
            string filter = string.Format("workcentername = '{0}' and teamname = '{1}'", dr["workcentername"], dr["teamname"]);

            DataRow[] drList = dtQueryData.Select(filter);

            if (drList.Length > 0)
            {
                dr["finishTime"] = Convert.ToDecimal(drList[0]["finishTime"]);
            }
            else
            {
                dr["finishTime"] = 0;
            }

            if (dr["planedTime"].ToString() == "0")
            {
                dr["finishRate"] = 0;
            }
            else
            {
                decimal finishRate = (Convert.ToDecimal(dr["finishTime"]) / Convert.ToDecimal(dr["planedTime"])) * 100;
                dr["finishRate"] = finishRate.ToString("0.00");
            }
        }
        Result r = new Result();

        foreach (DataColumn col in dtTeamname.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dtTeamname;
        r.total = Convert.ToInt32(dtTeamname.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}